﻿using System;
using System.Collections.Generic;
using Obj;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{


    /// <summary>
    /// Summary description for SQLUsuario
    /// </summary>
    public class SQLUsuario : dataAccess
    {

        #region Revisao

        public objUsuario SelectByChave(string chave)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_SelectByChave----------", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@chaveAcesso", SqlDbType.Int).Value = chave;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }
        public bool Delete(objUsuario usuario)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_Delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@usuarioID", SqlDbType.Int).Value = usuario.UsuarioID;
                con.Open();
                ExecuteReader(cmd);
                return true;
            }
        }
        public bool Update(objUsuario usuario)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@nome", SqlDbType.NVarChar).Value = usuario.Nome;
                cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = usuario.Email;
                cmd.Parameters.Add("@senha", SqlDbType.NVarChar).Value = usuario.Senha;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = usuario.Ativo;
                cmd.Parameters.Add("@permissaoID", SqlDbType.Int).Value = usuario.PermissaoID;
                cmd.Parameters.Add("@usuarioID", SqlDbType.Int).Value = usuario.UsuarioID;
                con.Open();
                ExecuteReader(cmd);
                return true;
            }
        }

        public int Insert(objUsuario usuario)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_Insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@nome", SqlDbType.NVarChar).Value = usuario.Nome;
                cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = usuario.Email;
                cmd.Parameters.Add("@senha", SqlDbType.NVarChar).Value = usuario.Senha;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = usuario.Ativo;                                                
                cmd.Parameters.Add("@permissaoID", SqlDbType.Int).Value = usuario.PermissaoID;                
                cmd.Parameters.Add("@chaveAcesso", SqlDbType.NVarChar).Value = usuario.Chave;
                cmd.Parameters.Add("@usuarioID", SqlDbType.Int).Direction = ParameterDirection.Output;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@usuarioID"].Value;
            }
        }




        #endregion

      


        public object selectByEmail(string email) 
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_SelectByEmail", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@email", SqlDbType.Text).Value = email;
                con.Open();
                return ExecuteScalar(cmd);
            }
        }

      

        public List<objUsuario> SelectAll()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        public List<objUsuario> SelectAll(objUsuario usuario)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Nome", SqlDbType.Text).Value = usuario.Nome;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }


        public objUsuario Select(objUsuario usuario)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@nome", SqlDbType.Text).Value = usuario.Nome;
                cmd.Parameters.Add("@senha", SqlDbType.Text).Value = usuario.Senha;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }



        public objUsuario SelectByID(int usuarioID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usuario_SelByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@usuarioID", SqlDbType.Int).Value = usuarioID;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }

        private objUsuario obj(IDataReader reader)
        {
            objUsuario usuario = new objUsuario(
                                                    reader["nome"].ToString(),
                                                    (int)reader["usuarioID"],
                                                    reader["email"].ToString(),
                                                    reader["senha"].ToString(),
                                                    (bool)reader["ativo"],
                                                    (int)reader["PermissaoID"],
                                                    reader["descricao"].ToString()
                                                );
            return usuario;
        }

        private List<objUsuario> objLista(IDataReader reader)
        {
            List<objUsuario> usuarios = new List<objUsuario>();
            while (reader.Read())
                usuarios.Add(obj(reader));
            return usuarios;

        }

    }
}


